package com.tro.common.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.tro.common.util.clazz.Tool;



/**
 * 
 *
 *
 *
 * Copyright (c) 2015年8月9日<br>
 * All rights reserved.<br>
 *
 * 文件名称：ExcelUtils.java<br>
 * 摘要：简要描述本文件的内容<br>
 * -------------------------------------------------------<br>
 * 当前版本：1.1.1<br>
 * 作者：lk<br>
 * 完成日期：2015年8月9日<br>
 * -------------------------------------------------------<br>
 * 取代版本：1.1.0<br>
 * 原作者：lk<br>
 * 完成日期：2015年8月9日<br>
 */
public class ExcelUtils {
	/**
	 * 根据模版导出Excel
	 * 
	 * @param sourceFilePath
	 *            模版文件位置
	 * @param targetFilePath
	 *            新文件位置
	 * @param report
	 *            数据Map
	 * @param xmlPath
	 *            配置文件位置
	 * @param sheetname
	 *            sheet 名
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static void CreateExcel(String sourceFilePath,
			String targetFilePath, List<Map> report, String xmlPath,
			String sheetname, int rownum, boolean isWidth)
			throws FileNotFoundException, IOException {
		boolean bool = true;
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				sourceFilePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);

		try {

			HSSFSheet sheet = wb.getSheetAt(0); // 创建一个Sheet
			wb.setSheetName(0, sheetname);
			sheet.autoSizeColumn(1, true);
			HSSFRow row;
			HSSFCell cell = null;

			HSSFCellStyle style = getStyle(wb);
			List<Map> pz = readXML(xmlPath);// 配置

			for (Map ap : report) {

				row = sheet.createRow(rownum);
				for (int i = 0; i < pz.size(); i++) {

					Map map1 = pz.get(i);

					String attrName = (String) map1.get("name");
					if (attrName == null) {
						continue;
					}

					String str = ap.get(attrName) == null ? "" : ap.get(
							attrName).toString();
					if (isWidth) {
						sheet.setColumnWidth(i, str.getBytes().length * 2 * 256);
					}
					myCreateCell(i, str, row, cell, style); // 列1

				}

				rownum++;
			}
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void CreateExcel(String[] header, String targetFilePath,
			List<Map> report, String xmlPath, String sheetname, boolean isWidth)
			throws FileNotFoundException, IOException {
		HSSFWorkbook wb = new HSSFWorkbook();

		try {

			HSSFSheet sheet = wb.createSheet(sheetname);
			sheet.autoSizeColumn(1, true);
			HSSFRow row;
			HSSFCell cell = null;

			HSSFCellStyle style = getStyle(wb);
			List<Map> pz = readXML(xmlPath);// 配置

			int rownum = 1;
			row = sheet.createRow(0);// 创建标题行
			for (int i = 0; i < header.length; i++) {
				myCreateCell(i, header[i], row, cell, style); // 列1
			}

			for (Map ap : report) {

				row = sheet.createRow(rownum);
				for (int i = 0; i < pz.size(); i++) {

					Map map1 = pz.get(i);

					String attrName = (String) map1.get("name");
					if (attrName == null) {
						continue;
					}

					String str = ap.get(attrName) == null ? "" : ap.get(
							attrName).toString();
					if (isWidth) {
						sheet.setColumnWidth(i, str.getBytes().length * 2 * 256);
					}
					myCreateCell(i, str, row, cell, style); // 列1

				}

				rownum++;
			}
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private static void myCreateCell(int cellnum, String value, HSSFRow row,
			HSSFCell cell, HSSFCellStyle style) {
		// CellValue cell71Val = evaluator.evaluate(cell71);
		// cell71.setCellValue(cell71Val.getNumberValue());
		cell = row.createCell(cellnum);
		cell.setCellValue(value);
		cell.setCellStyle(style);

	}

	public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		// 设置字体;
		HSSFFont font = workbook.createFont();
		// 设置字体大小;
		font.setFontHeightInPoints((short) 10);
		// 设置字体名字;
		font.setFontName("Courier New");
		// font.setItalic(true);
		// font.setStrikeout(true);
		// 设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		// 设置右边框;
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColor.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColor.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}

	public static List<Map> readXML(String xmlPath) {

		List<Map> list = new ArrayList<Map>();
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		try {
			DocumentBuilder db = dbf.newDocumentBuilder();
			File file = new File(xmlPath);
			Document doc = db.parse(file);

			NodeList dogList = doc.getElementsByTagName("column");
			for (int i = 0; i < dogList.getLength(); i++) {
				Node dog = dogList.item(i);
				Element elem = (Element) dog;
				Map<String, Object> map = new HashMap<String, Object>();
				for (Node node = dog.getFirstChild(); node != null; node = node
						.getNextSibling()) {
					if (node.getNodeType() == Node.ELEMENT_NODE) {
						String name = node.getNodeName();
						String value = node.getFirstChild().getNodeValue();
						map.put(name, value);
					}

				}
				list.add(map);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return 实体列表
	 */
	public static List<?> readExcelContent(InputStream is, int starRow,
			String name, String xmlPath, String fileType) {
		POIFSFileSystem fs;
		Workbook wb = null;
		org.apache.poi.ss.usermodel.Sheet sheet;
		Row row;
		List<Object> list = new ArrayList<Object>();
		Map<String, String> map = new HashMap<String, String>();
		try {

			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(is);

			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(is);
			} else {
				System.out.println("类型有误");
				return null;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		List<Map> pz = readXML(xmlPath);// 配置

		sheet = wb.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();

		for (int i = starRow; i <= rowNum; i++) {
			row = sheet.getRow(i);

			map = new HashMap<String, String>();
			for (int j = 0; j < pz.size(); j++) {

				Map map1 = pz.get(j);
				String attrName = (String) map1.get("name");
				map.put(attrName, getCellFormatValue(row.getCell(j)).trim());
			}
			Object obj = Tool.get(name);
			list.add(Tool.setValue(obj, map));
		}
		return list;
	}
	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private static String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 获取单元格数据内容为日期类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private static String getDateCellValue(HSSFCell cell) {
		String result = "";
		try {
			int cellType = cell.getCellType();
			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				Date date = cell.getDateCellValue();
				result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
						+ "-" + date.getDate();
			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				String date = getStringCellValue(cell);
				result = date.replaceAll("[年月]", "-").replace("日", "").trim();
			} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
				result = "";
			}
		} catch (Exception e) {
			System.out.println("日期格式不正确!");
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式

					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();

					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);

				}
				// 如果是纯数字
				else {
					// 取得当前Cell的数值
					DecimalFormat df = new DecimalFormat("0");  
					cellvalue = df.format(cell.getNumericCellValue()); 
					//cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}

	public static String getExtensionName(String filename) {
		if ((filename != null) && (filename.length() > 0)) {
			int dot = filename.lastIndexOf('.');
			if ((dot > -1) && (dot < (filename.length() - 1))) {
				return filename.substring(dot + 1);
			}
		}
		return filename;
	}
	
	public static String getRandomName(){
		String uuid = UUID.randomUUID().toString();
		return uuid;
	}

	
	
	
	private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
      
    /**
     * 
    * @Title: getBankListByExcel 
    * @Description: TODO(这里用一句话描述这个方法的作用) 
    * @param @param in
    * @param @param fileName
    * @param @param containTitle
    * @param @return
    * @param @throws Exception    设定文件 
    * @return List<List<Object>>    返回类型 
    * @throws 
    * @author leeko
     */
    public static List<List<Object>> getBankListByExcel(InputStream in,String fileName,Integer starRow,Integer colCount,List<Object> parameter) throws Exception{  
        List<List<Object>> list = null;  
          
        //创建Excel工作薄  
        Workbook work = getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("读取Excel工作薄为空！");  
        }  
        Sheet sheet = null;  
        Row row = null;  
        Cell cell = null;  
          
        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
            
            boolean flag=true;
            //遍历当前sheet中的所有行  
            for (int j = starRow; j <= sheet.getLastRowNum(); j++) { 
            	flag=true;
                row = sheet.getRow(j);  
                if(row==null){break;}
                if(row.getLastCellNum()!=colCount){
                	throw new Exception("模板有误,实际列数："+row.getLastCellNum());
                }
                
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { 
                	if(y==row.getFirstCellNum()){
                		//第一列如果没值
                		cell = row.getCell(y);
                		Object val=getCellValue(cell);
                		if(val==null||val.toString().equals("")){
                			flag=false;
                		}
                        li.add(getCellValue(cell));  
                	}else{
                		cell = row.getCell(y);
                        li.add(getCellValue(cell));  
                	}
                }  
                if(flag){
                	 if(parameter!=null){
                     	li.addAll(parameter);
                     }
                     list.add(li); 
                }
                
            }  
        }  
        return list;  
    } 
    
    public static List<List<Object>> getBankListByExcel(InputStream in,String fileName,Integer starRow,Integer colCount,List<Object> parameter,boolean asNull) throws Exception{  
        List<List<Object>> list = null;  
        
        //创建Excel工作薄  
        Workbook work = getWorkbook(in,fileName);  
        
        if(null == work){  
            throw new Exception("读取Excel工作薄为空！");  
        }  
        
        Sheet sheet = null;  
        Row row = null;  
        Cell cell = null;  
        FormulaEvaluator evaluator = work.getCreationHelper().createFormulaEvaluator(); 
        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
            
            boolean flag=true;
            //遍历当前sheet中的所有行  
            for (int j = starRow; j <= sheet.getLastRowNum(); j++) { 
            	flag=true;
                row = sheet.getRow(j);  
                if(row==null){break;}
                if(row.getLastCellNum()!=colCount){
                	throw new Exception("模板有误,实际列数："+row.getLastCellNum());
                }
                
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { 
                	if(y==row.getFirstCellNum()){
                		//第一列如果没值
                		
                		cell = row.getCell(y);
                		CellValue cellValue = evaluator.evaluate(cell);
                		Object val=getCellValue(cellValue);
                		if(val==null||val.toString().equals("")){
                			flag=false;
                		}
                        li.add(getCellValue(cell));  
                	}else{
                		cell = row.getCell(y);
                		CellValue cellValue = evaluator.evaluate(cell);
                		Object val=getCellValue(cellValue);
                		
                        
                		if(asNull){
                			if(val==null||val.toString().equals("")){
                    			val=null;
                    		}
                		}
                		li.add(val); 
                		 
                	}
                }  
                if(flag){
                	 if(parameter!=null){
                     	li.addAll(parameter);
                     }
                     list.add(li); 
                }
                
            }  
        }  
        return list;  
    } 
    
    
     /**
      * 
     * @Title: getWorkbook 
     * @Description: TODO(这里用一句话描述这个方法的作用) 
     * @param @param inStr
     * @param @param fileName
     * @param @return
     * @param @throws Exception    设定文件 
     * @return Workbook    返回类型 
     * @throws 
     * @author leeko
      */
    public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
  
    /**
     * 
    * @Title: getCellValue 
    * @Description: TODO(这里用一句话描述这个方法的作用) 
    * @param @param cell
    * @param @return    设定文件 
    * @return Object    返回类型 
    * @throws 
    * @author leeko
     */
    public static Object getCellValue(Cell cell){  
        Object value = null;  
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化  
        DecimalFormat df2 = new DecimalFormat("0");  //格式化数字  
        if(cell==null){
        	return "";
        }
        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            value = cell.getRichStringCellValue().getString();  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            if("General".equals(cell.getCellStyle().getDataFormatString())){  
            	if((cell.getNumericCellValue()+"").indexOf(".")!=-1){
            		 value = df2.format(cell.getNumericCellValue());  
            	}else{
                    value = df.format(cell.getNumericCellValue()); 
                }
            	 
            }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){  
                value = sdf.format(cell.getDateCellValue());  
            }else{  
                value = df2.format(cell.getNumericCellValue());  
            }  
            break;  
        
        case Cell.CELL_TYPE_BOOLEAN:  
            value = cell.getBooleanCellValue();  
            break;  
        case Cell.CELL_TYPE_BLANK:  
            value = "";  
            break;  
        default:  
            break;  
        }  
        return value;  
    }  
    //获取公式的值
    public static Object getCellValue(CellValue cell){  
    	 //System.out.println("123");
        Object value = null;  
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化  
        DecimalFormat df2 = new DecimalFormat("0");  //格式化数字  
        if(cell==null){
        	return "";
        }
        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            value = cell.getStringValue();  
           
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            value=cell.getNumberValue();
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = cell.getBooleanValue();  
            break;  
        case Cell.CELL_TYPE_BLANK:  
            value = "";  
            break;  
        default:  
            break;  
        }  
        return value;  
    }  
    
    public static void main(String[] args) throws Exception {
    	System.out.println(System.getProperty("user.dir"));
    	//File file=new File("/Users/apple/Downloads/upload/art.xlsx");
    	//InputStream in = new FileInputStream(file);
    	//System.out.println(JSON.toJSONString(ExcelUtils.getBankListByExcel(in,"test.xlsx",1,11,null,true)));
	}
}
